package db;
import java.sql.*;
import java.util.*;
import beans.*;
public class UserDAO extends ConnBean{
	private Connection connection=null;
	
	public boolean AdminReturn(CarRequestBean Record)
	{
		//PreparedStatement pstmt=null;
		String sql1="delete from CarRequest where RequestID='"+Record.getRequestID()+"'";
		String sql2="delete from EmployeeCarInfo where RequestID='"+Record.getRequestID()+"'";
		String sql3="delete from Driver_Task where RequestID='"+Record.getRequestID()+"'";
		
		if(Record==null) 
			return false;
		int result=0;
		try{
			connection=getConnection();			
			Statement stmt=null;//放在if或循环语句中，则只在语句段中有效
			ResultSet rs=null;//放在if或循环语句中，则只在语句段中有效
			//准备执行数据库插入，此处使用了?方式，比拼接字符串更清晰
			stmt=connection.createStatement();
			//执行数据库查询，获取结果集
			result=stmt.executeUpdate(sql1);
			result=stmt.executeUpdate(sql2);
			result=stmt.executeUpdate(sql3);
			//关闭表达式对象
			stmt.close();			
			//关闭数据库							
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public List<Driver_TaskBean> getDriver_Task_AllRecords()
	{
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <Driver_TaskBean> list=new ArrayList<Driver_TaskBean>();
		try {		
			connection = getConnection();
			if(connection==null)
				System.out.println("error");

			String sql="select * from Driver_Task";

			stmt =connection.createStatement();

			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Driver_TaskBean user=new Driver_TaskBean();
				user.setDid(rs.getString(1));;
				user.setRequestID(rs.getString(2));
				user.setUsePhone(rs.getString(3));
				user.setapplicant(rs.getString(4));
				user.setUersNum(rs.getInt(5));
				user.setUseTime(rs.getString(6));
				user.setReturnTime(rs.getString(7));				
				user.setinstructions(rs.getString(8));				
				user.setkm(rs.getString(9));
				list.add(user);
			}

			stmt.close();			
		
			connection.close();			
			
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
			return list;
	}
	
	public List<CarRequestBean> getCarRequest_AllRecords()
	{
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <CarRequestBean> list=new ArrayList<CarRequestBean>();
		try {		
			connection = getConnection();
			if(connection==null)
				System.out.println("error");

			String sql="select * from CarRequest";

			stmt =connection.createStatement();

			rs=stmt.executeQuery(sql);
			while(rs.next()){
				CarRequestBean user=new CarRequestBean();
				user.setRequestID(rs.getString(1));
				user.setUsersNum(rs.getInt(2));
				user.setUseRange(rs.getString(3));
				user.setUserphone(rs.getString(4));
				user.setUseTime(rs.getString(5));
				user.setReturnTime(rs.getString(6));
				user.setdegree(rs.getString(7));
				user.setEmpID(rs.getString(8));
				user.setapplyTime(rs.getString(9));
				user.setapplyDepartment(rs.getString(10));
				user.setorigin(rs.getString(11));
				user.setgoPlace(rs.getString(12));
				user.setUseReasons(rs.getString(13));
				user.setRemarks(rs.getString(14));
				user.setAttachment(rs.getString(15));
				user.setAgreeOrNot(rs.getString(16));
				user.setCensorContent(rs.getString(17));
				user.setAdminname(rs.getString(18));
				user.setAdminTime(rs.getString(19));
				user.setDid(rs.getString(20));
				user.setStatus(rs.getString(21));
				user.setlicense_plate(rs.getString(22));
				user.setinstructions(rs.getString(23));				
				user.setkm(rs.getString(24));
				list.add(user);
			}

			stmt.close();			
		
			connection.close();			
			
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
			return list;
	}
	
	public AdminBean getAdmin_getRecord(String ID){
		ResultSet rs=null;
		Statement stmt=null;
		AdminBean user=null;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from Admin where ID='"+ID+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				System.out.println("right");
				user=new AdminBean();
				user.setID(rs.getString(1));
				user.setAdminname(rs.getString(2));
				user.setpassword(rs.getString(3));
				user.setdepartment(rs.getString(4));
			}
			else
			{	
				System.out.println("null");
				user=null;
			}
			stmt.close();					
			connection.close();	

		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return user;
  }	
	
	public CarRequestBean getCarRequest_getRecord(String RequestID){
		ResultSet rs=null;
		Statement stmt=null;
		CarRequestBean user=null;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from CarRequest where RequestID='"+RequestID+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				System.out.println("right");
				user=new CarRequestBean();
				user.setRequestID(rs.getString(1));
				user.setUsersNum(rs.getInt(2));
				user.setUseRange(rs.getString(3));
				user.setUserphone(rs.getString(4));
				user.setUseTime(rs.getString(5));
				user.setReturnTime(rs.getString(6));
				user.setdegree(rs.getString(7));
				user.setEmpID(rs.getString(8));
				user.setapplyTime(rs.getString(9));
				user.setapplyDepartment(rs.getString(10));
				user.setorigin(rs.getString(11));
				user.setgoPlace(rs.getString(12));
				user.setUseReasons(rs.getString(13));
				user.setRemarks(rs.getString(14));
				user.setAttachment(rs.getString(15));
				user.setAgreeOrNot(rs.getString(16));
				user.setCensorContent(rs.getString(17));
				user.setAdminname(rs.getString(18));
				user.setAdminTime(rs.getString(19));
				user.setDid(rs.getString(20));
				user.setStatus(rs.getString(21));
				user.setlicense_plate(rs.getString(22));
				user.setinstructions(rs.getString(23));				
				user.setkm(rs.getString(24));
			}
			else
			{	
				System.out.println("null");
				user=null;
			}
			stmt.close();					
			connection.close();	

		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return user;
  }	
	
	public boolean login_valid(String ID,String password,String department)
	{
		ResultSet rs=null;
		Statement stmt=null;
		boolean result=false;
		try {		
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql=null;
			if("司机".equals(department))
				sql="select Did from Driver where Did='"+ID+"' and Dpassword='"+password+"' ";
			else if("职员".equals(department))
				sql="select EmpID from Employee where EmpID='"+ID+"' and password='"+password+"'  ";
			else 
				sql="select id from Admin where id='"+ID+"' and password='"+password+"' and department='"+department+"' ";

			stmt =connection.createStatement();

			rs=stmt.executeQuery(sql);
			if(!rs.next())
				result=false;
			else
				result=true;

			stmt.close();			
	
			connection.close();	

		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
			return result;		
	}
	
	public boolean enroll_valid(String EmpID)
	{
		ResultSet rs=null;
		Statement stmt=null;
		boolean result=false;
		try {		
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select EmpID,password from Employee where EmpID='"+EmpID+"'";

			stmt =connection.createStatement();

			rs=stmt.executeQuery(sql);
			if(!rs.next())
				result=false;
			else
				result=true;

			stmt.close();			
	
			connection.close();	

		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
			return result;		
	}
	
	public boolean enroll_update(EmployeeBean Record)
	{
		if(Record==null) 
			return false;
		String ID=null;
		String password=null;
		PreparedStatement pstmt=null;
		String sql="update Employee set password=?,phone=? where EmpID='"+Record.getEmpID()+"'";
		String sql0="select EmpID,password from Employee where EmpID='"+Record.getEmpID()+"'";
		int result=0;
		try{
			connection=getConnection();
			
			Statement stmt =connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql0);
			if (rs.next()){
				ID=rs.getString(1);	//获取第1个字段
				password=rs.getString(2);
			}
			stmt.close();
			if(ID!=null&&password==null)
			{
				pstmt=connection.prepareStatement(sql);
				pstmt.setString(1,Record.getpassword());
				pstmt.setString(2,Record.getPhone());
				result=pstmt.executeUpdate();
				pstmt.close();	
			}
			else
				result=0;
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public boolean AdminSubmit_edit(CarRequestBean Record)
	{
		if(Record==null) 
			return false;
		PreparedStatement pstmt=null;
		String sql="update CarRequest set AgreeOrNot=?,CensorContent=?,Adminname=?,AdminTime=?,Status=? where RequestID='"+Record.getRequestID()+"' ";
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,Record.getAgreeOrNot());
			pstmt.setString(2,Record.getCensorContent());
			pstmt.setString(3,Record.getAdminname());
			pstmt.setString(4,Record.getAdminTime());
			pstmt.setString(5,Record.getStatus());
			result=pstmt.executeUpdate();
			pstmt.close();	
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public boolean staff_insertCarRequest(CarRequestBean record)
	{
		PreparedStatement pstmt=null;
		String sql="insert into CarRequest(RequestID,UsersNum,UseRange,Userphone,UseTime,ReturnTime,degree,EmpID,applyTime,applyDepartment,origin,goPlace,UseReasons,Remarks,Attachment,Status,Did,license_plate) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		if(record==null) 
			return false;
		boolean result=false;
		int number=0;
		try{
			connection=getConnection();

			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,record.getRequestID());
			pstmt.setInt(2,record.getUsersNum());
			pstmt.setString(3,record.getUseRange());
			pstmt.setString(4,record.getUserphone());
			pstmt.setString(5,record.getUseTime());
			pstmt.setString(6,record.getReturnTime());
			pstmt.setString(7,record.getdegree());
			pstmt.setString(8,record.getEmpID());
			pstmt.setString(9,record.getapplyTime());
			pstmt.setString(10,record.getapplyDepartment());
			pstmt.setString(11,record.getorigin());
			pstmt.setString(12,record.getgoPlace());
			pstmt.setString(13,record.getUseReasons());
			pstmt.setString(14,record.getRemarks());
			pstmt.setString(15,record.getAttachment());
			pstmt.setString(16,record.getStatus());
			pstmt.setString(17,record.getDid());
			pstmt.setString(18,record.getlicense_plate());
			number=pstmt.executeUpdate();
			//�رձ��ʽ����
			pstmt.close();			
			//�ر����ݿ�			
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		//System.out.println("***"+number);
		if(number>0)
			result=true;
		return result;
	}
	
	public boolean staff_insertInfo(EmployeeCarInfoBean record)
	{
		PreparedStatement pstmt=null;
		String sql="insert into EmployeeCarInfo(RequestID,EmpID,UseTime,ReturnTime,Did,Dname,Dphone,license_plate,UsersNum,origin,goPlace) values(?,?,?,?,?,?,?,?,?,?,?)";
		if(record==null) 
			return false;
		boolean result=false;
		int number=0;
		try{
			connection=getConnection();

			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,record.getRequestID());
			pstmt.setString(2,record.getEmpID());
			pstmt.setString(3,record.getUseTime());
			pstmt.setString(4,record.getReturnTime());
			pstmt.setString(5,record.getDid());
			pstmt.setString(6,record.getDname());
			pstmt.setString(7,record.getDphone());
			pstmt.setString(8,record.getlicense_plate());
			pstmt.setInt(9,record.getUsersNum());
			pstmt.setString(10,record.getorigin());
			pstmt.setString(11,record.getgoPlace());
			number=pstmt.executeUpdate();
			//�رձ��ʽ����
			pstmt.close();			
			//�ر����ݿ�			
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		//System.out.println("***"+number);
		if(number>0)
			result=true;
		return result;
	}
	
	public String getLastRequestID(){
		ResultSet rs=null;
		Statement stmt=null;
		String ID=null;
		String RequestID=null;
		int max=1;
		try {		
			connection = getConnection();
			if(connection==null)
				System.out.println("error");

			String sql="select RequestID from CarRequest ORDER BY RequestID";

			stmt =connection.createStatement();

			rs=stmt.executeQuery(sql);			
			while(rs.next()){
				ID=rs.getString("RequestID");
				int IDInt=Integer.parseInt(ID);
				if(IDInt>max)
					max=IDInt;
			}
			System.out.println("max:"+max);
			if(ID!=null)
				RequestID=String.valueOf(max+1);
			else
				RequestID="1";
			stmt.close();			
		
			connection.close();	

		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		System.out.println("R:"+RequestID);
		return RequestID;
  }	
}
